﻿Public Class frmProduct
    Dim dsProduct As New DataSet
    Dim daProduct As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim ableComma1, ableComma2, ableComma3, ableComma4 As String

    Private Sub btnNew_Click(sender As System.Object, e As System.EventArgs) Handles btnNew.Click
        Call txtReset()
    End Sub

    Private Sub txtReset()
        txtCompCd.Clear()
        txtRemarks.Clear()
        txtPart.Clear()
        txtSize.Clear()
        txtPrdNm.Clear()
        txtPrdCd.Clear()
        txtUnitInPrice.Clear()
        txtBox2Ea.Clear()
        txtBarCode.Clear()
        txtUnitB2BPrice.Clear()
        txtUnitB2CPrice.Clear()
        cmbPrdGrp.SelectedIndex = 0
        cmbFlag.SelectedIndex = 0
    End Sub

    Private Sub frmProduct_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        e.Cancel = True
        Me.Hide()
    End Sub

    Private Sub frmProduct_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call gSubIconShow(Me)
        gridRefresh("default")
        mkCmbPrdGrp()
    End Sub


    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        gridRefresh("")

    End Sub

    Private Sub btnSearchReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchReset.Click
        txtSearch.Clear()
        gridRefresh("")
    End Sub

    Private Sub txtSearch_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtSearch.KeyPress
        If Asc(e.KeyChar) = 13 Then
            gridRefresh("")
        End If
    End Sub

    Private Sub txtUnitPrice_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtUnitInPrice.KeyPress
        If Not Char.IsDigit(e.KeyChar) And Not Char.IsControl(e.KeyChar) Then
            e.Handled = True
            ableComma1 = ""
        Else
            ableComma1 = "ok"
        End If
    End Sub

    Private Sub txtUnitPrice_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtUnitInPrice.KeyUp
        If ableComma1 = "ok" Then
            gSubWithComma(txtUnitInPrice)
        End If
    End Sub

    Private Sub txtBox2Ea_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBox2Ea.KeyPress
        If Not Char.IsDigit(e.KeyChar) And Not Char.IsControl(e.KeyChar) Then
            e.Handled = True
            ableComma2 = ""
        Else
            ableComma2 = "ok"
        End If
    End Sub

    Private Sub txtBox2Ea_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtBox2Ea.KeyUp
        If ableComma2 = "ok" Then
            gSubWithComma(txtBox2Ea)
        End If
    End Sub

    Private Sub txtUnitB2CPrice_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtUnitB2CPrice.KeyPress
        If Not Char.IsDigit(e.KeyChar) And Not Char.IsControl(e.KeyChar) Then
            e.Handled = True
            ableComma3 = ""
        Else
            ableComma3 = "ok"
        End If
    End Sub

    Private Sub txtUnitB2CPrice_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtUnitB2CPrice.KeyUp
        If ableComma3 = "ok" Then
            gSubWithComma(txtUnitB2CPrice)
        End If
    End Sub
    Private Sub txtUnitB2BPrice_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtUnitB2BPrice.KeyPress
        If Not Char.IsDigit(e.KeyChar) And Not Char.IsControl(e.KeyChar) Then
            e.Handled = True
            ableComma3 = ""
        Else
            ableComma3 = "ok"
        End If
    End Sub

    Private Sub txtUnitB2BPrice_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtUnitB2BPrice.KeyUp
        If ableComma3 = "ok" Then
            gSubWithComma(txtUnitB2BPrice)
        End If
    End Sub

    Private Sub btnLookupInventory_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLookupInventory.Click
        Dim prdCd As String = ""
        If txtPrdCd.Text <> "" Then
            MsgBox("[" + txtPrdNm.Text + "]의 재고조사를 진행합니다")
            prdCd = txtPrdCd.Text
            Call gLookupInventory(prdCd, "")
        Else

            Dim result As Integer = MessageBox.Show("전 제품의 재고조사를 진행합니다. " + vbCrLf + "수 초에서 수분이 소요될 수 있습니다." + vbCrLf + "진행하시겠습니까?", "InventoryMan", MessageBoxButtons.YesNo)
            If result = DialogResult.No Then
                Exit Sub
            ElseIf result = DialogResult.Yes Then
                MsgBox("준비중입니다.")
                Exit Sub
            End If
        End If
        gridRefresh("")
        Call txtReset()
    End Sub

    Private Sub cmbFlag_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbFlag.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmbPrdGrp_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbPrdGrp.KeyPress
        e.Handled = True
    End Sub

#Region "프로시져"

    Private Sub gridRefresh(ByVal initFlag As String)
        Call dbConnection()
        Dim addSql As String = ""

        dsProduct = New DataSet
        If txtSearch.Text <> "" Then
            addSql = " AND prdNm LIKE '%" + txtSearch.Text + "%' "
        End If
        If initFlag = "default" Then
            addSql = " AND A.flag='N' "
        End If
        sql = "SELECT A.prdCd as 제품코드, A.prdNm as 제품명, B.itemNm as  제품그룹, A.[part] as 규격1, A.barcode as 바코드, A.[size] as 규격2, A.unitInPrice as 입고단가, A.inventoryCnt as 재고, A.unitB2CPrice as 출고소매가, A.unitB2BPrice as 출고도매가, A.box2Ea as 박수당갯수, A.compCd as 기타코드 " & _
            "   FROM tblProduct A LEFT JOIN (SELECT itemCd, itemNm FROM tblComCode WHERE groupCd='0001' AND flag='Y') B ON A.prdGrp=B.itemCd WHERE A.flag='Y'" + addSql + _
            "   ORDER BY A.prdNm, A.[part]"
        'MsgBox(sql)

        Try
            daProduct = New OleDb.OleDbDataAdapter(sql, dbConn)
            daProduct.Fill(dsProduct, "PRODUCT")

            grdProduct.DataSource = dsProduct.Tables("PRODUCT")
            lbGridCnt.Text = "총 " + dsProduct.Tables("PRODUCT").Rows.Count.ToString + " 건"
            dbConn.Close()
            Call setGridDesign()

        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    Private Sub setGridDesign()

        grdProduct.Columns(0).Visible = False
        grdProduct.Columns(1).Width = "150"
        grdProduct.Columns(2).Width = "100"
        grdProduct.Columns(3).Width = "90"
        grdProduct.Columns(4).Width = "80"
        grdProduct.Columns(5).Width = "90"
        grdProduct.Columns(6).Width = "90"
        grdProduct.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdProduct.Columns(6).DefaultCellStyle.Format = "n0"
        grdProduct.Columns(7).Width = "90"
        grdProduct.Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdProduct.Columns(7).DefaultCellStyle.Format = "n0"
        grdProduct.Columns(8).Width = "90"
        grdProduct.Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdProduct.Columns(8).DefaultCellStyle.Format = "n0"
        grdProduct.Columns(9).Width = "90"
        grdProduct.Columns(9).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdProduct.Columns(9).DefaultCellStyle.Format = "n0"
        grdProduct.Columns(10).Width = "80"
        grdProduct.Columns(10).DefaultCellStyle.Format = "n0"
        grdProduct.Columns(11).Width = "120"
        grdProduct.Columns(11).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft

        With grdProduct.DefaultCellStyle
            .Alignment = DataGridViewContentAlignment.MiddleCenter

        End With
        grdProduct.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft

        gSubMkCmbFlag(cmbFlag) '## 삭제 여부 콤보박스 초기화
        cmbFlag.SelectedIndex = 0
        'grdProduct.Columns(3).Frozen = True

        For rowIndex As Integer = grdProduct.Rows.Count - 2 To 0 Step -1
            If grdProduct.Rows(rowIndex).Cells(1).Value = grdProduct.Rows(rowIndex + 1).Cells(1).Value Then
                grdProduct.Rows(rowIndex + 1).Cells(1).Value = ""
            End If
        Next
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Call dbConnection()
        Dim oleCmd As OleDb.OleDbCommand
        Dim oleTran As OleDb.OleDbTransaction
        Dim rtnMsg As String
        Dim valPrdNm, valPrdCd, valPrdGrp, valPart, valCompCd, valSize, valUnitInPrice, valUnitB2CPrice, valUnitB2BPrice, valRemarks, valBarcode, valFlag, valBox2Ea As String
        Dim curDt As DateTime = Now

        valPrdNm = gFnChkVal(txtPrdNm.Text)
        valPrdCd = txtPrdCd.Text
        valPart = gFnChkVal(txtPart.Text)
        valCompCd = gFnChkVal(txtCompCd.Text)
        valSize = gFnChkVal(txtSize.Text)
        valUnitInPrice = gFnChkVal(txtUnitInPrice.Text)
        valUnitB2CPrice = gFnChkVal(txtUnitB2CPrice.Text)
        valUnitB2BPrice = gFnChkVal(txtUnitB2BPrice.Text)
        valRemarks = gFnChkVal(txtRemarks.Text)
        valBarcode = gFnChkVal(txtBarCode.Text)
        valBox2Ea = txtBox2Ea.Text
        If valBox2Ea = "" Then
            valBox2Ea = 0
        End If
        If valUnitInPrice = "" Then
            valUnitInPrice = 0
        End If
        If valUnitB2CPrice = "" Then
            valUnitB2CPrice = 0
        End If
        If valUnitB2BPrice = "" Then
            valUnitB2BPrice = 0
        End If
        valPrdGrp = cmbPrdGrp.SelectedValue
        valFlag = cmbFlag.SelectedValue

        If valPrdNm = "" Then
            MsgBox("제품명을 바르게 입력해 주세요")
            txtPrdNm.Focus()
            dbConn.Close()
            Exit Sub
        End If


        If txtPrdCd.Text = "" Then
            sql = "INSERT INTO tblProduct(prdNm, prdGrp, part, remarks, compCd, unitInPrice, unitB2CPrice, unitB2BPrice, [size],  box2Ea, barcode, flag, issueDt) " + _
                "   VALUES('" + valPrdNm + "','" + valPrdGrp + "','" + valPart + "','" + valRemarks + "','" + valCompCd + "','" + valUnitInPrice + "','" + valUnitB2CPrice + "','" + valUnitB2BPrice + "','" + valSize + "','" + valBox2Ea + "','" + valBarcode + "','" + valFlag + "','" + curDt + "')"
            rtnMsg = "제품 정보가 등록되었습니다"
        Else
            sql = "UPDATE tblProduct SET prdNm='" + valPrdNm + "', prdGrp='" + valPrdGrp + "', part='" + valPart + "', remarks='" + valRemarks + "', compCd='" + valCompCd + "', unitInPrice='" + valUnitInPrice + "', unitB2CPrice='" + valUnitB2CPrice + "', unitB2BPrice='" + valUnitB2BPrice + "', [size]='" + valSize + "', box2Ea=" + valBox2Ea + ", barcode='" + valBarcode + "', flag='" + valFlag + "', issueDt='" + curDt + "' WHERE prdCd=" + txtPrdCd.Text
            rtnMsg = "제품 정보가 수정되었습니다"
        End If

        oleCmd = New OleDb.OleDbCommand(sql, dbConn)

        oleTran = dbConn.BeginTransaction
        oleCmd.Transaction = oleTran
        Try

            oleCmd.ExecuteNonQuery()
            oleTran.Commit()

            dbConn.Close()
            MsgBox(rtnMsg)
            gridRefresh("")
            Call txtReset()
        Catch err As OleDb.OleDbException
            oleTran.Rollback()
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    Private Sub mkCmbPrdGrp()

        sql = "SELECT itemCd, itemNm FROM tblComCode WHERE flag='Y' AND groupCd='0001' ORDER BY itemSeq"
        dsProduct = New DataSet
        daProduct = New OleDb.OleDbDataAdapter(sql, dbConn)
        daProduct.Fill(dsProduct, "cmbPrdGrp")

        dbConn.Close()
        With cmbPrdGrp
            .DataSource = dsProduct.Tables("cmbPrdGrp")
            .DisplayMember = "itemNm"
            .ValueMember = "itemCd"
            .SelectedIndex = 0
        End With
    End Sub

    Private Sub grdProduct_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdProduct.CellClick
        If e.RowIndex = -1 Then
            Exit Sub
        End If
        If IsDBNull(grdProduct.Rows(e.RowIndex).Cells(0).Value) = True Then
            Exit Sub
        End If
        sql = "SELECT prdCd, prdNm, remarks, part, prdGrp, compCd, unitInPrice, [size], flag, box2Ea, unitB2CPrice, unitB2BPrice, barcode FROM tblProduct WHERE prdCd=" & grdProduct.Rows(e.RowIndex).Cells(0).Value

        Call dbConnection()
        dsProduct = New DataSet
        daProduct = New OleDb.OleDbDataAdapter(sql, dbConn)
        daProduct.Fill(dsProduct, "productInfo")

        dbConn.Close()

        txtReset()

        txtPrdCd.Text = dsProduct.Tables("productInfo").Rows(0).Item(0)
        txtPrdNm.Text = dsProduct.Tables("productInfo").Rows(0).Item(1)

        If IsDBNull(dsProduct.Tables("productInfo").Rows(0).Item(2)) = False Then
            txtRemarks.Text = dsProduct.Tables("productInfo").Rows(0).Item(2)
        End If
        If IsDBNull(dsProduct.Tables("productInfo").Rows(0).Item(3)) = False Then
            txtPart.Text = dsProduct.Tables("productInfo").Rows(0).Item(3)
        End If
        If IsDBNull(dsProduct.Tables("productInfo").Rows(0).Item(4)) = False Then
            cmbPrdGrp.SelectedValue = dsProduct.Tables("productInfo").Rows(0).Item(4)
        End If
        If IsDBNull(dsProduct.Tables("productInfo").Rows(0).Item(5)) = False Then
            txtCompCd.Text = dsProduct.Tables("productInfo").Rows(0).Item(5)
        End If
        If IsDBNull(dsProduct.Tables("productInfo").Rows(0).Item(6)) = False Then
            txtUnitInPrice.Text = dsProduct.Tables("productInfo").Rows(0).Item(6)
            txtUnitInPrice.Text = Format(CInt(txtUnitInPrice.Text), "#,##0")
        End If
        If IsDBNull(dsProduct.Tables("productInfo").Rows(0).Item(7)) = False Then
            txtSize.Text = dsProduct.Tables("productInfo").Rows(0).Item(7)
        End If
        If IsDBNull(dsProduct.Tables("productInfo").Rows(0).Item(9)) = False Then
            txtBox2Ea.Text = dsProduct.Tables("productInfo").Rows(0).Item(9)
        End If
        If IsDBNull(dsProduct.Tables("productInfo").Rows(0).Item(10)) = False Then
            txtUnitB2CPrice.Text = dsProduct.Tables("productInfo").Rows(0).Item(10)
            txtUnitB2CPrice.Text = Format(CInt(txtUnitB2CPrice.Text), "#,##0")
        End If
        If IsDBNull(dsProduct.Tables("productInfo").Rows(0).Item(11)) = False Then
            txtUnitB2BPrice.Text = dsProduct.Tables("productInfo").Rows(0).Item(11)
            txtUnitB2BPrice.Text = Format(CInt(txtUnitB2BPrice.Text), "#,##0")
        End If
        If IsDBNull(dsProduct.Tables("productInfo").Rows(0).Item(12)) = False Then
            txtBarCode.Text = dsProduct.Tables("productInfo").Rows(0).Item(12)
        End If
    End Sub

#End Region

    Private Sub btn_xlsDown_Click(sender As System.Object, e As System.EventArgs) Handles btn_xlsDown.Click
        If FolderBrowserDlg.ShowDialog() = Windows.Forms.DialogResult.OK Then
            lbTarget.Text = FolderBrowserDlg.SelectedPath + "/제품.xlsx"
        Else
            Exit Sub
        End If

        If grdProduct.RowCount = 0 Then
            MsgBox("먼저 제품을 조회해 주세요")
            lbTarget.Text = ""
            Exit Sub
        End If

        gGrid2Excel(grdProduct, lbTarget.Text, btn_xlsDown, prgressBar, 0)

    End Sub

    Private Sub lbTarget_Click(sender As System.Object, e As System.EventArgs) Handles lbTarget.Click
        If lbTarget.Text <> "" Then
            Process.Start(lbTarget.Text)
        End If
    End Sub

    Private Sub btnAddProduct_Click(sender As System.Object, e As System.EventArgs) Handles btnAddProduct.Click
        If txtPrdCd.Text = "" Then
            MsgBox("등록된 제품 하나를 선택해 주세요")
            Exit Sub
        End If
        txtPrdCd.Clear()
        txtPart.Clear()
        txtSize.Clear()
        txtBarCode.Clear()
    End Sub

    Private Sub btnXlsUp_Click(sender As System.Object, e As System.EventArgs) Handles btnXlsUp.Click
        frmXLSProductUp.Show()
    End Sub
End Class